﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Odbc;
using Framework;

namespace MySqlConnection
{
    public class DBContainer : IDisposable
    {
        private const string MyConString = "SERVER=localhost;DATABASE=db_home;UID=root;";
        private MySql.Data.MySqlClient.MySqlConnection _connection;
        private static DBContainer instance = null;
        
        public MySql.Data.MySqlClient.MySqlConnection Connection
        {
            get { return _connection; }
        }

        private DBContainer()
	    {
            this._connection = new MySql.Data.MySqlClient.MySqlConnection(MyConString);
        }

        public static DBContainer GetInstance()
        {

            if (instance == null)
            {
                instance = new DBContainer();
            }
            return instance;
        }

        #region Images

        /// <summary>
        /// Add an Image item in the data base with given parameters
        /// </summary>
        /// <param name="name"></param>
        /// <param name="desc"></param>
        /// <param name="createDate"></param>
        /// <param name="ext"></param>
        /// <param name="data"></param>
        /// <param name="size"></param>
        public void CreateImage(string name, string desc, string createDate, string ext, byte[] data, int size)
        {
            string req = "insert into images (name,description, created, extension, data, size) "
                                                 + "values (@name, @description, @created, @extension, @data, @size)";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    command.Parameters.Add(new MySqlParameter("@name", name));
                    command.Parameters.Add(new MySqlParameter("@description", desc));
                    command.Parameters.Add(new MySqlParameter("@created", createDate));
                    command.Parameters.Add(new MySqlParameter("@extension", ext));
                    MySqlParameter dataParam = new MySqlParameter("@data", MySqlDbType.Binary);
                    dataParam.Value = data;
                    command.Parameters.Add(dataParam);
                    command.Parameters.Add(new MySqlParameter("@size", size));
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {

            }
            finally
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// Update an image item in the data base
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="desc"></param>
        public void UpdateImage(string id, string name, string desc)
        {
            string req = "update images set name = @name, description = @description where id=@id";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    command.Parameters.Add(new MySqlParameter("@id", id));
                    command.Parameters.Add(new MySqlParameter("@name", name));
                    command.Parameters.Add(new MySqlParameter("@description", desc));
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {

            }
            finally
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// Retreive an image item by a given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ImageClass GetImageById(string id)
        {
            ImageClass img = new ImageClass();
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                string req = "select * from images where id = '" + id + "'";
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            img.id = MappingTool.CatchNull(rd.GetValue(0));
                            img.name = MappingTool.CatchNull(rd.GetValue(1));
                            img.description = MappingTool.CatchNull(rd.GetValue(2));
                            img.data = (byte[])rd.GetValue(5);
                            img.ext = MappingTool.CatchNull(rd.GetValue(4));
                            img.size = MappingTool.CatchIntNull(rd.GetValue(6));
                            img.src = MappingTool.CatchNull(rd.GetValue(7));
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return img;
        }

        /// <summary>
        /// Retreive all image items stored in data base
        /// </summary>
        /// <returns></returns>
        public List<ImageClass> GetAllImages()
        {
            List<ImageClass> allImgs = new List<ImageClass>();
            string storedProcAllImages = "Get_All_Images";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAllImages, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            ImageClass img = new ImageClass();
                            img.id = MappingTool.CatchNull(rd.GetValue(0));
                            img.name = MappingTool.CatchNull(rd.GetValue(1));
                            img.description = MappingTool.CatchNull(rd.GetValue(2));
                            img.data = (byte[])rd.GetValue(5);
                            img.ext = MappingTool.CatchNull(rd.GetValue(4));
                            img.size = MappingTool.CatchIntNull(rd.GetValue(6));
                            img.src = MappingTool.CatchNull(rd.GetValue(7));
                            allImgs.Add(img);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return allImgs;
        }
        #endregion

        #region News

        public void AddNews(NewsClass news)
        {
            string storedProcAddNews = "Insert_News";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAddNews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("title", news.title));
                    command.Parameters.Add(new MySqlParameter("created", news.created) { DbType = DbType.Date});
                    command.Parameters.Add(new MySqlParameter("modified", news.modified) { DbType = DbType.Date });
                    command.Parameters.Add(new MySqlParameter("description", news.description));
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            { }
            finally
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// Retreive all image items stored in data base
        /// </summary>
        /// <returns></returns>
        public List<NewsClass> GetAllNews()
        {
            List<NewsClass> allNews = new List<NewsClass>();
            string storedProcAllINews = "Get_All_News";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAllINews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            NewsClass news = new NewsClass();
                            news.id = MappingTool.CatchNull(rd.GetValue(0));
                            news.title = MappingTool.CatchNull(rd.GetValue(1));
                            news.created = MappingTool.CatchNull(rd.GetValue(2));
                            news.modified = MappingTool.CatchNull(rd.GetValue(3));
                            allNews.Add(news);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return allNews;
        }

        public void DeleteNews(string id)
        {
            string storedProcDeleteNews = "Delete_News";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcDeleteNews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("news_id", id));
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            { }
            finally
            {
                Connection.Close();
            }
        }

        public NewsClass GetNewsById(string id)
        {
            NewsClass news = new NewsClass();
            string storedProcAllINews = "Get_News_By_Id";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAllINews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("id", id));
                    using (MySqlDataReader rd = command.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        while (rd.Read())
                        {
                            news.id = MappingTool.CatchNull(rd.GetValue(0));
                            news.title = MappingTool.CatchNull(rd.GetValue(1));
                            news.created = MappingTool.CatchNull(rd.GetValue(2));
                            news.modified = MappingTool.CatchNull(rd.GetValue(3));
                            news.description = MappingTool.CatchNull(rd.GetValue(4));
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return news;
        }


        #endregion

        #region Paragraph
        public void AddParagraph(ParagraphClass paragraph)
        {
            string storedProcAddNews = "Insert_Paragraph";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAddNews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("title", paragraph.title));
                    command.Parameters.Add(new MySqlParameter("description", paragraph.description));
                    command.Parameters.Add(new MySqlParameter("content", paragraph.content));
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            { }
            finally
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// Retreive all image items stored in data base
        /// </summary>
        /// <returns></returns>
        public List<ParagraphClass> GetAllParagraphs()
        {
            List<ParagraphClass> allParagraphs = new List<ParagraphClass>();
            string storedProcAllINews = "Get_All_Paragraphs";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAllINews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            ParagraphClass paragraph = new ParagraphClass();
                            paragraph.id = MappingTool.CatchNull(rd.GetValue(0));
                            paragraph.title = MappingTool.CatchNull(rd.GetValue(1));
                            paragraph.description = MappingTool.CatchNull(rd.GetValue(2));
                            paragraph.content = MappingTool.CatchNull(rd.GetValue(3));
                            allParagraphs.Add(paragraph);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return allParagraphs;
        }

        public ParagraphClass GetParagraphById(string id)
        {
            ParagraphClass paragraph = new ParagraphClass();
            string storedProcAllINews = "Get_Paragraph_By_Id";
            try
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                using (MySqlCommand command = new MySqlCommand(storedProcAllINews, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("id", id));
                    using (MySqlDataReader rd = command.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        while (rd.Read())
                        {
                            paragraph.id = MappingTool.CatchNull(rd.GetValue(0));
                            paragraph.title = MappingTool.CatchNull(rd.GetValue(1));
                            paragraph.description = MappingTool.CatchNull(rd.GetValue(2));
                            paragraph.content = MappingTool.CatchNull(rd.GetValue(3));
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return paragraph;
        }

        public void UpdateParagraph(ParagraphClass paragraph)
        {
            string storedProcUpdateParagraph = "Update_Paragraph";
            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                using (MySqlCommand command = new MySqlCommand(storedProcUpdateParagraph, Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new MySqlParameter("id", paragraph.id));
                    command.Parameters.Add(new MySqlParameter("title", paragraph.title));
                    command.Parameters.Add(new MySqlParameter("description", paragraph.description));
                    command.Parameters.Add(new MySqlParameter("content", paragraph.content));
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {

            }
            finally
            {
                Connection.Close();
            }
        }

        #endregion
        public void Dispose()
        {
            throw new NotImplementedException();
        }
    }
}
